package com.glela.common.util;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**********************************************
 * @author gwq
 * @date 2017.11.08
 * @version 1.0.1 <br/>
 **********************************************/
public class ExcelUtil {
	private static String			dir;
	private static String			filename;
	private static SimpleDateFormat	df	= new SimpleDateFormat("yyyy年MM月dd日hh时mm分ss秒");
	
	/**
	 * 生成多个sheet的excel
	 * @throws Exception
	 */
	public static void writeExecl(String full_filename, Map<String, List<Map<String, Object>>> result, Map<String, String[]> names, Map<String, String> keyword) throws Exception {
		if(StringUtils.isBlank(full_filename)){
		full_filename=File.separator+"异常数据"+File.separator+"("+df.format(new Date())+ ")异常数据.xlsx";
		}
		if(!full_filename.startsWith(File.separator)){
			full_filename=File.separator+full_filename;
		}
		if(!StringUtils.endsWithAny(full_filename,".xlsx",".xls")){
	       throw new Exception("文件格式不是excel格式");
		  }
		dir = full_filename.substring(0, full_filename.lastIndexOf(File.separator));
		filename = full_filename.substring(full_filename.lastIndexOf(File.separator) + 1, full_filename.length());
		File path = new File(dir);
		if (!path.exists()) {
			path.mkdirs();
		}
		// 创建一个webbook，对应一个Excel文件
		//在内存中只保留100行记录,超过100就将之前的存储到磁盘里
		Workbook wb = new SXSSFWorkbook(100);
		//从result中读取每个sheet的内容
		for (int i = 1; i <= result.size(); i++) {
			// 创建sheet 并生成excel文件
			if(result.get(i + "")==null||names.get(i + "")==null|| keyword.get(i + "")==null){
				continue;
			}
			creatSheet(wb, i-1, result.get(i + ""), names.get(i + ""), keyword.get(i + ""));
		}
		// 将文件存到指定位置
		try {
			FileOutputStream fout = new FileOutputStream(FileUtils.getFile(dir, filename));
			wb.write(fout);
			fout.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 生成sheet
	 */
	public static void creatSheet(Workbook wb, int sheetNum, List<Map<String, Object>> sheetData, String[] namesArr, String keyword) {
		// 创建sheet
		Sheet sheet = wb.createSheet();
		wb.setSheetName(sheetNum, keyword);
		// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		Row row = sheet.createRow(0);
		// 创建单元格，并设置值表头 设置表头居中
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
		// 设置第一行标题
		Cell cell = null;
		for (int i = 0; i < namesArr.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(namesArr[i]);
			cell.setCellStyle(style);
		}
		// 写入实体数据
		Map<String, Object> map = new HashMap<String, Object>();
		for (int i = 0; i < sheetData.size(); i++) {
			//创建表格，并填装数据
			row = sheet.createRow(i + 1);
			map = sheetData.get(i);
			for (int k = 0; k < map.size(); k++) {
				row.createCell(k).setCellValue(map.get((k+1)+"") == null ? "" : map.get((k+1)+"").toString());
			}
		}
	}
	public static void main(String[] args) {
		try {
		String full_filename="ss\\("+df.format(new Date())+ ")异常数据.xls"; 
//		String full_filename=null; 
//		String full_filename="c:/ss/异常数据.xlsx"; 
		Map<String, List<Map<String, Object>>> result=new HashMap<>();
		List<Map<String, Object>> list=new ArrayList<>();
		Map<String, Object> map=new HashMap<String, Object>();;
		map.put("0", "1");
		map.put("1", "2");
		map.put("2", "3");
		map.put("3", "4");
		list.add(map);
		result.put("1", list);
		Map<String, String[]> names=new HashMap<>();
		String[] str={"11","22","33","44"};
		names.put("1", str);
		Map<String, String> keyword=new HashMap<>();
		keyword.put("1", "异常订单");
			writeExecl(full_filename,result,names,keyword);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
	}
}
